﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Net;
using System.Diagnostics;

namespace DnsCatcher.Service
{
    public class DatabaseInterface
    {
        public static string CN_STRING = "Data Source=D:\\DnsQueries.s3db";

        public static void InsertQuery(IPAddress sourceIp, string data, DateTime utcTime)
        {
            SQLiteConnection cn = new SQLiteConnection(DatabaseInterface.CN_STRING);
            SQLiteCommand cmd = cn.CreateCommand();

            try
            {
                cmd.CommandText = "SELECT ID FROM Addresses WHERE IPv4Address = @IPv4Address";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@IPv4Address", sourceIp);
                cn.Open();

                object val = cmd.ExecuteScalar();
                if (val == null)
                {
                    cmd.CommandText = "INSERT INTO Addresses(IPv4Address) VALUES(@IPv4Address)";
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                Logger.LogException(e);
            }
            finally
            {
                if (cn.State == System.Data.ConnectionState.Open)
                    cn.Close();
            }

            try
            {
                cmd.CommandText = "SELECT ID FROM Hostnames WHERE RecordData = @RecordData";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@RecordData", data);
                cn.Open();

                object val = cmd.ExecuteScalar();
                if (val == null)
                {
                    cmd.CommandText = "INSERT INTO Hostnames(RecordType,RecordData) VALUES('A',@RecordData)";
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                Logger.LogException(e);
            }
            finally
            {
                if (cn.State == System.Data.ConnectionState.Open)
                    cn.Close();
            }

            try
            {
                cmd.CommandText = "INSERT INTO Queries(AddressID,HostnameID,DateValue,TimeValue) " +
                    "SELECT a.ID, h.ID, @Date, @Time FROM Addresses a, Hostnames h " +
                    "WHERE a.IPv4Address = @IPv4Address AND h.RecordData = @RecordData;";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@Date", utcTime);
                cmd.Parameters.AddWithValue("@IPv4Address", sourceIp.ToString());
                cmd.Parameters.AddWithValue("@RecordData", data);
                cmd.Parameters.AddWithValue("@Time", utcTime);

                cn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Logger.LogException(e);
            }
            finally
            {
                cmd.Dispose();

                if (cn.State == System.Data.ConnectionState.Open)
                    cn.Close();

                cn.Dispose();
            }
        }
    }

    public class Address
    {
        private int _id;
        public int ID
        {
            get { return this._id; }
            internal set { this._id = value; }
        }

        private IPAddress _ipV4Address;
        public IPAddress IPv4Address
        {
            get { return this._ipV4Address; }
            set { this._ipV4Address = value; }
        }
    }

    public class Hostname
    {
        private int _id;
        public int ID
        {
            get { return this._id; }
            internal set { this._id = value; }
        }

        private string _recordType;
        public string RecordType
        {
            get { return this._recordType; }
            set { this._recordType = value; }
        }
    }

    public class Query
    {
        private int _id;
        public int ID
        {
            get { return this._id; }
            internal set { this._id = value; }
        }

        private float _timeValue;
        public float TimeValue
        {
            get { return this._timeValue; }
            set { this._timeValue = value; }
        }

        private int _addressId;
        public Address SourceAddress
        {
            get
            {
                SQLiteConnection cn = new SQLiteConnection(DatabaseInterface.CN_STRING);
                SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Addresses WHERE ID = @AddressID", cn);
                cmd.Parameters.AddWithValue("@AddressID", this._addressId);

                try
                {
                    cn.Open();
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new Address()
                            {
                                ID = Convert.ToInt32(reader["ID"]),
                                IPv4Address = IPAddress.Parse(reader["IPv4Address"].ToString())
                            };
                        }
                    }
                }
                catch (Exception e)
                {
                    Logger.LogException(e);
                }
                finally
                {
                    cmd.Dispose();
                    cn.Close();
                    cn.Dispose();
                }

                return null;
            }
            set
            {
                this._addressId = value.ID;
            }
        }

        private int _hostnameId;
        public Hostname QueryData
        {
            get
            {
                SQLiteConnection cn = new SQLiteConnection(DatabaseInterface.CN_STRING);
                SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Hostnames WHERE ID = @HostnameID", cn);
                cmd.Parameters.AddWithValue("@HostnameID", this._hostnameId);

                try
                {
                    cn.Open();
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new Hostname()
                            {
                                ID = Convert.ToInt32(reader["ID"]),
                                RecordType = reader["RecordType"].ToString()
                            };
                        }
                    }
                }
                catch (Exception e)
                {
                    Logger.LogException(e);
                }
                finally
                {
                    cmd.Dispose();
                    cn.Close();
                    cn.Dispose();
                }

                return null;
            }
            set
            {
                this._hostnameId = value.ID;
            }
        }
    }
}
